<?php
class Dbtool{

    private $conn;
    private $hostname;
    private $username;
    private $password;
    private $database;
    private $port;
    private $socket;

    function __construct($hostname="localhost",$username="root",$password="root",$database="ts_task",$port="3306",$socket=null){
       $this->hostname = $hostname;
       $this->username = $username;
       $this->password = $password;
       $this->database = $database;
       $this->port = $port;
       $this->socket = $socket;
    }

    private function connect(){
        $conn = new mysqli(
            $this->hostname,
            $this->username,
            $this->password,
            $this->database,
            $this->port,
            $this->socket,
        );
        // 检测连接
        if ($conn->connect_error) {
            echo Json::Fail(1, "服务器错误", null);
            die("连接失败: " . $conn->connect_error);
        } 
        $this->conn = $conn;
    }

    function query_sql($sql){
//        usleep(200000);
        $this->connect();
        $result = $this->conn->query($sql);
        $this->conn->close();
        return $result;
    }

    function select($tableName, $fields='*', $where=1, $orderby=null, $pagination=null){
        if(is_array($fields)){
            $fieldsTemp = '';
            foreach($fields as $v){
                $fieldsTemp = $fieldsTemp.$v.", ";
            }
            $fieldsTemp = substr($fieldsTemp, 0, -2);
            $fields = $fieldsTemp;
        }
        if(is_array($where)){
            $temp = "";
            foreach($where as $k => $v){
                if($k == "update_time"){
                    $k = "DATE_FORMAT(update_time, '%Y-%m-%d')";
                    $v = date('Y-m-d', $v);
                }
                $temp = $temp.$k."='".$v."' AND ";
            }
            $temp = substr($temp, 0, -4);
            $where = $temp;
        }
        if(!empty($orderby)){
            $where=$where.' ORDER BY '.$orderby;
        }
        if(is_array($pagination)){
            $page = $pagination["page"];
            $size = $pagination["size"];
            $suffix = " LIMIT ".$size." OFFSET ".(($page-1)*$size);
            $pagination = $suffix;
        }
        $sql = "SELECT ". $fields . " FROM `".$tableName."` WHERE ".$where.($pagination?:'');
        $result = $this->query_sql($sql);
//         echo $sql;
        if (isset($result) && $result->num_rows > 0) {
            $arr = [];
            // 输出数据
            while($row = $result->fetch_assoc()) {
                $arr[] = $row;
            }
            return $arr;
        } else {
            return null;
        }
    }

    function update($tableName, $value, $where=1){
        $sql = "UPDATE `". $tableName."` SET ";
        foreach($value as $k => $v){
            $sql = $sql."`".$k."`='".$v."', ";
        }
        $sql = substr($sql, 0, -1);
        if(is_array($where)){
            $temp = " ";
            foreach($where as $k => $v){
                $temp = $temp.$k."='".$v."' AND ";
            }
            $temp = substr($temp, 0, -4).";";
            $where = $temp;
        }
        $sql = substr($sql, 0, -1)." WHERE ".$where;
        return $this->query_sql($sql);
    }

    function insert($tableName, $value){
        $sql = "INSERT INTO `".$tableName."` (";
        foreach($value as $k => $v){
            $sql = $sql . $k . ", ";
        }
        $sql = substr($sql, 0, -2).") VALUES ('";
        foreach($value as $k => $v){
            $sql = $sql . $v . "', '";
        }
        $sql = substr($sql, 0, -3).")".";";
        return $this->query_sql($sql);
    }
}
